package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;
import model.common.JobSituation;

public class JobSituationDao {
	
	public void addJobSituation(JobSituation j) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+"insert into tab_jobsituation " + "(employeeName,employeeAge,employeeSex,"
				    + " employeeEmail,employeeAddress,employeeIdentifyId,employeeTelephone,entryTime,jobStatus) "
				    + " values(?,?,?,?,?,?,?,?,?)  ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, j.getEmployeeName());
		ptmt.setString(2, j.getEmployeeAge());
		ptmt.setString(3, j.getEmployeeSex());
		ptmt.setString(4, j.getEmployeeEmail());
		ptmt.setString(5, j.getEmployeeAddress());
		ptmt.setString(6, j.getEmployeeIdentifyId());
		ptmt.setString(7, j.getEmployeeTelephone());
		ptmt.setDate(8, new java.sql.Date(j.getEntryTime().getTime()));
		ptmt.setString(9, j.getJobStatus());

		ptmt.execute();
	}

	public void deleteJobSituation(Integer employeeId) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " delete from tab_jobsituation  where employeeId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, employeeId);
		ptmt.execute();
	}
	
	public void updateJobSituation(JobSituation j) throws SQLException{
		Connection conn=DBHelper.getConnection();
		String sql=" update tab_jobsituation set employeeName=?,employeeAge=?,employeeSex=?,employeeEmail=?,"
		+ " employeeAddress=?,employeeIdentifyId=?,employeeTelephone=?,entryTime=?,jobStatus=? "
				+ " where employeeId=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		ptmt.setString(1, j.getEmployeeName());
		ptmt.setString(2, j.getEmployeeAge());
		ptmt.setString(3, j.getEmployeeSex());
		ptmt.setString(4, j.getEmployeeEmail());
		ptmt.setString(5, j.getEmployeeAddress());
		ptmt.setString(6, j.getEmployeeIdentifyId());
		ptmt.setString(7, j.getEmployeeTelephone());
		ptmt.setDate(8, new java.sql.Date(j.getEntryTime().getTime()));
		ptmt.setString(9, j.getJobStatus());
		ptmt.setInt(10, j.getEmployeeId());
		
		ptmt.execute();
	}

	public JobSituation get(Integer employeeId) throws SQLException {
		JobSituation j = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_jobsituation where employeeId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, employeeId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			j = new JobSituation();
			j.setEmployeeId(rs.getInt("employeeId"));
			j.setEmployeeName(rs.getString("employeeName"));
			j.setEmployeeAge(rs.getString("employeeAge"));
			j.setEmployeeSex(rs.getString("EmployeeSex"));
			j.setEmployeeEmail(rs.getString("EmployeeEmail"));
			j.setEmployeeAddress(rs.getString("employeeAddress"));
			j.setEmployeeIdentifyId(rs.getString("EmployeeIdentifyId"));
			j.setEmployeeTelephone(rs.getString("EmployeeTelephone"));
			j.setEntryTime(rs.getDate("entryTime"));
			j.setJobStatus(rs.getString("jobStatus"));
		}
		return j;
	}
	
	public List<JobSituation> query(List<Map<String, Object>> params) throws SQLException{
		List<JobSituation> result=new ArrayList<JobSituation>(); 
		Connection conn = DBHelper.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("select * from tab_jobsituation where 1=1 ");
		if(params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String, Object> map=params.get(i);
				sb.append(" and " +map.get("name")+"  " +map.get("rela")+"  " +map.get("value"));
			}
		}
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		JobSituation j = null;
		while (rs.next()) {
		j=new JobSituation();
			j.setEmployeeId(rs.getInt("employeeId"));
			j.setEmployeeName(rs.getString("employeeName"));
			j.setEmployeeAge(rs.getString("employeeAge"));
			j.setEmployeeSex(rs.getString("EmployeeSex"));
			j.setEmployeeEmail(rs.getString("EmployeeEmail"));
			j.setEmployeeAddress(rs.getString("employeeAddress"));
			j.setEmployeeIdentifyId(rs.getString("EmployeeIdentifyId"));
			j.setEmployeeTelephone(rs.getString("EmployeeTelephone"));
			j.setEntryTime(rs.getDate("entryTime"));
			j.setJobStatus(rs.getString("jobStatus"));
			result.add(j);
		}
		
		return result;
		}
		
	public List<JobSituation> query() throws Exception{
		List<JobSituation> result=new ArrayList<JobSituation>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select employeeId,employeeName,employeeAge from tab_jobsituation  ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		
		ResultSet rs=ptmt.executeQuery();
		
		JobSituation j=null;
		while(rs.next()){
			j=new JobSituation();
			j.setEmployeeId(rs.getInt("employeeId"));
			j.setEmployeeName(rs.getString("employeeName"));
			j.setEmployeeAge(rs.getString("employeeAge"));
			result.add(j);
		}
		
		return result;
		
	}
	
	public List<JobSituation> query(String employeeName,String employeeAge) throws Exception{
		List<JobSituation> result=new ArrayList<JobSituation>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from tab_jobsituation  ");
		
		sb.append(" where employeeName like ? and employeeAge like ? ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		ptmt.setString(1, "%"+employeeName+"%");
		ptmt.setString(2, "%"+employeeAge+"%");
		
		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		JobSituation j=null;
		while(rs.next()){
			j=new JobSituation();
			j.setEmployeeId(rs.getInt("employeeId"));
			j.setEmployeeName(rs.getString("employeeName"));
			j.setEmployeeAge(rs.getString("employeeAge"));
			j.setEmployeeSex(rs.getString("employeeSex"));
			j.setEmployeeEmail(rs.getString("employeeEmail"));
			j.setEmployeeAddress(rs.getString("employeeAddress"));
			j.setEmployeeIdentifyId(rs.getString("employeeIdentifyId"));
			j.setEmployeeTelephone(rs.getString("employeeTelephone"));
			j.setEntryTime(rs.getDate("entryTime"));
			j.setJobStatus(rs.getString("jobStatus"));
			result.add(j);
		}
		return result;
	}
	
}
